﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace TodayWhoModel
{
    public class UserModel
    {
        #region Fields

        private int userId;

        public int UserId
        {
            get { return userId; }
            set { userId = value; }
        }
        private string email;

        public string Email
        {
            get { return email; }
            set { email = value; }
        }
        private string password;

        public string Password
        {
            get { return password; }
            set { password = value; }
        }
        private string userName;

        public string UserName
        {
            get { return userName; }
            set { userName = value; }
        }
        private DateTime signupDate;

        public DateTime SignupDate
        {
            get { return signupDate; }
            set { signupDate = value; }
        }
        private DateTime lastLoginDate;

        public DateTime LastLoginDate
        {
            get { return lastLoginDate; }
            set { lastLoginDate = value; }
        }
        private DateTime lastActivityDate;

        public DateTime LastActivityDate
        {
            get { return lastActivityDate; }
            set { lastActivityDate = value; }
        }

        private int categoryId;

        public int CategoryId
        {
            get { return categoryId; }
            set { categoryId = value; }
        }
        private string nameSurname;

        public string NameSurname
        {
            get { return nameSurname; }
            set { nameSurname = value; }
        }
        private string title;

        public string Title
        {
            get { return title; }
            set { title = value; }
        }

        private string shortDesc;

        public string ShortDesc
        {
            get { return shortDesc; }
            set { shortDesc = value; }
        }
        private string facebook;

        public string Facebook
        {
            get { return facebook; }
            set { facebook = value; }
        }
        private string twitter;

        public string Twitter
        {
            get { return twitter; }
            set { twitter = value; }
        }
        private string blogger;

        public string Blogger
        {
            get { return blogger; }
            set { blogger = value; }
        }
        private string wordpress;

        public string Wordpress
        {
            get { return wordpress; }
            set { wordpress = value; }
        }
        private string linkedin;

        public string Linkedin
        {
            get { return linkedin; }
            set { linkedin = value; }
        }
        private string tumblr;

        public string Tumblr
        {
            get { return tumblr; }
            set { tumblr = value; }
        }
        private string formspring;

        public string Formspring
        {
            get { return formspring; }
            set { formspring = value; }
        }
        private string stumbleupon;

        public string Stumbleupon
        {
            get { return stumbleupon; }
            set { stumbleupon = value; }
        }
        private string flickr;

        public string Flickr
        {
            get { return flickr; }
            set { flickr = value; }
        }
        private string picasa;

        public string Picasa
        {
            get { return picasa; }
            set { picasa = value; }
        }
        private string myspace;

        public string Myspace
        {
            get { return myspace; }
            set { myspace = value; }
        }
        private string lastfm;

        public string Lastfm
        {
            get { return lastfm; }
            set { lastfm = value; }
        }
        private string youtube;

        public string Youtube
        {
            get { return youtube; }
            set { youtube = value; }
        }
        private string foursquare;

        public string Foursquare
        {
            get { return foursquare; }
            set { foursquare = value; }
        }
        private string website1;

        public string Website1
        {
            get { return website1; }
            set { website1 = value; }
        }
        private string website2;

        public string Website2
        {
            get { return website2; }
            set { website2 = value; }
        }
        private string website3;

        public string Website3
        {
            get { return website3; }
            set { website3 = value; }
        }

        private string instagram;

        public string Instagram
        {
            get { return instagram; }
            set { instagram = value; }
        }
        private string googleplus;

        public string Googleplus
        {
            get { return googleplus; }
            set { googleplus = value; }
        }
        private string vimeo;

        public string Vimeo
        {
            get { return vimeo; }
            set { vimeo = value; }
        }
        private string github;

        public string Github
        {
            get { return github; }
            set { github = value; }
        }
        private string stackoverflow;

        public string Stackoverflow
        {
            get { return stackoverflow; }
            set { stackoverflow = value; }
        }
        private string pinterest;

        public string Pinterest
        {
            get { return pinterest; }
            set { pinterest = value; }
        }
        private string soundcloud;

        public string Soundcloud
        {
            get { return soundcloud; }
            set { soundcloud = value; }
        }
        private string skype;

        public string Skype
        {
            get { return skype; }
            set { skype = value; }
        }

        private bool isTherePhoto;

        public bool IsTherePhoto
        {
            get { return isTherePhoto; }
            set { isTherePhoto = value; }
        }
        private bool isPhotoUsed;

        public bool IsPhotoUsed
        {
            get { return isPhotoUsed; }
            set { isPhotoUsed = value; }
        }

        private string photoUrl1;

        public string PhotoUrl1
        {
            get { return photoUrl1; }
            set { photoUrl1 = value; }
        }
        private string photoUrl2;

        public string PhotoUrl2
        {
            get { return photoUrl2; }
            set { photoUrl2 = value; }
        }
        private string fbUserId;

        public string FbUserId
        {
            get { return fbUserId; }
            set { fbUserId = value; }
        }

        private string fbToken;

        public string FbToken
        {
            get { return fbToken; }
            set { fbToken = value; }
        }

        private bool fbIsComplete;

        public bool FbIsComplete
        {
            get { return fbIsComplete; }
            set { fbIsComplete = value; }
        }


        #endregion

        #region Database operations

        // checked
        public bool IsUserNameUnique()
        {
            string ssql = "SELECT * FROM [Users] WHERE username=@userName";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@userName", UserName);

            SqlDataReader reader = DatabaseHelper.ExecuteSelectSql(cmd);
            if (reader.HasRows)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        // checked
        public bool IsEmailUnique()
        {
            string ssql = "SELECT * FROM [Users] WHERE email=@email";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@email", Email);

            SqlDataReader reader = DatabaseHelper.ExecuteSelectSql(cmd);
            if (reader.HasRows)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        // checked
        public int Insert()
        {
            string ssql = "INSERT INTO [Users] (email,password,username,categoryId) ";
            ssql += " VALUES(@email,@password,@username,@categoryId); SET @userId = SCOPE_IDENTITY() ";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.Add("@userId", System.Data.SqlDbType.Int);
            cmd.Parameters[0].Direction = System.Data.ParameterDirection.Output;
            cmd.Parameters.AddWithValue("@email", Email);
            cmd.Parameters.AddWithValue("@password", Password);
            cmd.Parameters.AddWithValue("@username", UserName);
            cmd.Parameters.AddWithValue("@categoryId", CategoryId);

            int userId = DatabaseHelper.ExecuteInsertSqlWithIdentity(cmd);
            if (userId != 0)
            {
                UserId = userId;
                UserDisplayModel userDisplay = new UserDisplayModel();
                userDisplay.UserId = userId;
                UserPreferencesModel userPrefs = new UserPreferencesModel();
                userPrefs.UserId = userId;
                if (userDisplay.Insert() && userPrefs.Insert())
                {
                    return userId;
                }
                else
                {
                    return 0;
                }
            }
            else
            {
                return 0;
            }
        }

        // checked
        public bool UpdateEmail()
        {
            string ssql = "UPDATE [Users] SET email=@email  WHERE userId=@userId";
            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@email", Email);

            return DatabaseHelper.ExecuteNonQuerySqlWithoutIdentity(cmd);
        }

        // checked
        public bool UpdateCategory()
        {
            string ssql = "UPDATE [Users] SET categoryId=@categoryId  WHERE userId=@userId";
            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@categoryId", CategoryId);

            return DatabaseHelper.ExecuteNonQuerySqlWithoutIdentity(cmd);
        }

        public bool UpdatePersonalInfo()
        {
            string ssql = "UPDATE [Users] SET nameSurname=@nameSurname, shortDesc=@shortDesc, categoryId=@categoryId  WHERE userId=@userId";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@nameSurname", NameSurname);
            cmd.Parameters.AddWithValue("@shortDesc", ShortDesc);
            cmd.Parameters.AddWithValue("@categoryId", CategoryId);

            return DatabaseHelper.ExecuteNonQuerySqlWithoutIdentity(cmd);
        }

        public bool UpdateSocialNetworkInfo()
        {
            string ssql = @"UPDATE [Users] SET facebook=@facebook, twitter=@twitter, blogger=@blogger,  
                                               wordpress=@wordpress, linkedin=@linkedin, tumblr=@tumblr,
                                               formspring=@formspring, stumbleupon=@stumbleupon, flickr=@flickr,
                                               picasa=@picasa, myspace=@myspace, lastfm=@lastfm,
                                               youtube=@youtube, foursquare=@foursquare, website1=@website1,
                                               website2=@website2, website3=@website3  WHERE userId=@userId";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@facebook", Facebook);
            cmd.Parameters.AddWithValue("@twitter", Twitter);
            cmd.Parameters.AddWithValue("@blogger", Blogger);
            cmd.Parameters.AddWithValue("@wordpress", Wordpress);
            cmd.Parameters.AddWithValue("@linkedin", Linkedin);
            cmd.Parameters.AddWithValue("@tumblr", Tumblr);
            cmd.Parameters.AddWithValue("@formspring", Formspring);
            cmd.Parameters.AddWithValue("@stumbleupon", Stumbleupon);
            cmd.Parameters.AddWithValue("@flickr", Flickr);
            cmd.Parameters.AddWithValue("@picasa", Picasa);
            cmd.Parameters.AddWithValue("@myspace", Myspace);
            cmd.Parameters.AddWithValue("@lastfm", Lastfm);
            cmd.Parameters.AddWithValue("@youtube", Youtube);
            cmd.Parameters.AddWithValue("@foursquare", Foursquare);
            cmd.Parameters.AddWithValue("@website1", Website1);
            cmd.Parameters.AddWithValue("@website2", Website2);
            cmd.Parameters.AddWithValue("@website3", Website3);

            return DatabaseHelper.ExecuteNonQuerySqlWithoutIdentity(cmd);
        }
        
        public bool UpdatePhotoInfo()
        {
            string ssql = "UPDATE [Users] SET isTherePhoto=@isTherePhoto, photoUrl1=@photoUrl1, photoUrl2=@photoUrl2  WHERE userId=@userId";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@isTherePhoto", IsTherePhoto);
            cmd.Parameters.AddWithValue("@photoUrl1", PhotoUrl1);
            cmd.Parameters.AddWithValue("@photoUrl2", PhotoUrl2);

            return DatabaseHelper.ExecuteNonQuerySqlWithoutIdentity(cmd);
        }

        // checked
        public bool UpdatePassword()
        {
            string ssql = "UPDATE [Users] SET password=@password WHERE userId=@userId";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@password", Password);

            return DatabaseHelper.ExecuteNonQuerySqlWithoutIdentity(cmd);
        }

        // checked
        public bool CheckLogin()
        {
            string ssql = "SELECT * FROM [Users] WHERE email=@email AND password=@password ";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@email", email);
            cmd.Parameters.AddWithValue("@password", password);

            SqlDataReader reader = DatabaseHelper.ExecuteSelectSql(cmd);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    this.UserId = Int32.Parse(reader["userId"].ToString());
                    this.Email = reader["email"].ToString();
                    this.Password = reader["password"].ToString();
                    this.UserName = reader["userName"].ToString();
                    if (reader["categoryId"] != null)
                    {
                        this.CategoryId = Int32.Parse(reader["categoryId"].ToString());
                    }
                }
                return true;
            }
            else
            {
                return false;
            }
        }

        // checked
        public int InsertFbNew()
        {
            string ssql = "INSERT INTO [Users] (email,password,username,nameSurname,fbUserId,fbToken) ";
            ssql += " VALUES(@email,@password,@username,@nameSurname,@fbUserId,@fbToken); SET @userId = SCOPE_IDENTITY() ";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.Add("@userId", System.Data.SqlDbType.Int);
            cmd.Parameters[0].Direction = System.Data.ParameterDirection.Output;
            cmd.Parameters.AddWithValue("@email", Email);
            cmd.Parameters.AddWithValue("@password", "ahdkjasjfskdj*g?+_fsdl123a$$712kfjsdbfdasdas");
            cmd.Parameters.AddWithValue("@username", FbUserId+Email);
            cmd.Parameters.AddWithValue("@nameSurname", NameSurname);
            cmd.Parameters.AddWithValue("@fbUserId", FbUserId);
            cmd.Parameters.AddWithValue("@fbToken", FbToken);

            int userId = DatabaseHelper.ExecuteInsertSqlWithIdentity(cmd);
            if (userId != 0)
            {
                UserId = userId;
                UserDisplayModel userDisplay = new UserDisplayModel();
                userDisplay.UserId = userId;
                UserPreferencesModel userPrefs = new UserPreferencesModel();
                userPrefs.UserId = userId;
                if (userDisplay.Insert() && userPrefs.Insert())
                {
                    return userId;
                }
                else
                {
                    return 0;
                }
            }
            else
            {
                return 0;
            }
        }

        // checked
        public bool InsertFbExisting()
        {
            string ssql = "UPDATE [Users] SET fbUserId=@fbUserId,fbToken=@fbToken  WHERE email=@email";
            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@email", Email);
            cmd.Parameters.AddWithValue("@fbUserId", FbUserId);
            cmd.Parameters.AddWithValue("@fbToken", FbToken);

            return DatabaseHelper.ExecuteNonQuerySqlWithoutIdentity(cmd);
        }

        // checked
        public bool CheckFblogin()
        {
            string ssql = "SELECT * FROM [Users] WHERE email=@email";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@email", Email);

            SqlDataReader reader = DatabaseHelper.ExecuteSelectSql(cmd);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    this.UserId = Int32.Parse(reader["userId"].ToString());
                    this.Email = reader["email"].ToString();
                    this.Password = reader["password"].ToString();
                    this.UserName = reader["userName"].ToString();
                    if (reader["categoryId"].ToString() != "")
                    {
                        this.CategoryId = Int32.Parse(reader["categoryId"].ToString());
                    }
                    this.FbIsComplete = Boolean.Parse(reader["fbIsComplete"].ToString());
                }
                return true;
            }
            else
            {
                return false;
            }
        }

        // checked
        public bool UpdateUserNameAndCategory()
        {
            string ssql = "UPDATE [Users] SET categoryId=@categoryId,username=@username  WHERE userId=@userId";
            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@username", UserName);
            cmd.Parameters.AddWithValue("@categoryId", CategoryId);

            return DatabaseHelper.ExecuteNonQuerySqlWithoutIdentity(cmd);
        }

        // checked
        public bool UpdateFBComplete()
        {
            string ssql = "UPDATE [Users] SET fbIsComplete=@fbIsComplete  WHERE userId=@userId";
            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@fbIsComplete", FbIsComplete);

            return DatabaseHelper.ExecuteNonQuerySqlWithoutIdentity(cmd);
        }

        // checked
        public bool ChangePassword()
        {
            bool res = false;
            string ssql = "SELECT * FROM [Users] WHERE email=@email";
            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@email", Email);
            SqlDataReader reader = DatabaseHelper.ExecuteSelectSql(cmd);
            if (reader.HasRows)
            {
                reader.Close();
                ssql = "UPDATE [Users] SET password=@password WHERE email=@email";
                SqlCommand cmdUpdate = new SqlCommand(ssql);
                cmdUpdate.Parameters.AddWithValue("@email", Email);
                Helper helper = new Helper();
                string password = helper.RandomString(8);
                cmdUpdate.Parameters.AddWithValue("@password", password);

                res = DatabaseHelper.ExecuteNonQuerySqlWithoutIdentity(cmdUpdate);
                if (res)
                {
                    Mailer.SendPasswordRecoveryEmail(email, password);
                }
            }
            return res;

        }

        // checked
        public void RetrieveUserIdWithUserName()
        { 
            string ssql = "SELECT * FROM [Users]  WHERE username=@username ";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@username", UserName);


            SqlDataReader reader = DatabaseHelper.ExecuteSelectSql(cmd);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    this.UserId = Int32.Parse(reader["userId"].ToString());
                }
            }
        }

        // checked
        public static string RetrievePasswordWithUserName(String userName)
        {
            string ssql = "SELECT * FROM [Users]  WHERE username=@username ";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@username", userName);
            string password="";

            SqlDataReader reader = DatabaseHelper.ExecuteSelectSql(cmd);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    password = reader["password"].ToString();
                }
            }
            return password;
        }

        
        public void RetrieveUserInfoWithUserName()
        {
            string ssql = "SELECT * FROM [Users] as U INNER JOIN [User-Displays] as UD ON U.userId=UD.userId WHERE username=@username AND isActive=1 ";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@username", UserName);


            SqlDataReader reader = DatabaseHelper.ExecuteSelectSql(cmd);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    this.UserId = Int32.Parse(reader["userId"].ToString());
                    this.Email = reader["email"].ToString();
                    this.Password = reader["password"].ToString();
                    this.UserName = reader["username"].ToString();
                    this.NameSurname = reader["nameSurname"].ToString();
                    this.Title = reader["title"].ToString();
                    this.ShortDesc = reader["shortDesc"].ToString();
                    if (reader["categoryId"].ToString() != "")
                    {
                        this.CategoryId = Convert.ToInt32(reader["categoryId"].ToString());
                    }
                    else
                    {
                        this.CategoryId = 0;
                    }
                    this.Facebook = reader["facebook"].ToString();
                    this.Twitter = reader["twitter"].ToString();
                    this.Blogger = reader["blogger"].ToString();
                    this.Wordpress = reader["wordpress"].ToString();
                    this.Linkedin = reader["linkedin"].ToString();
                    this.Tumblr = reader["tumblr"].ToString();
                    this.Formspring = reader["formspring"].ToString();
                    this.Stumbleupon = reader["stumbleupon"].ToString();
                    this.Flickr = reader["flickr"].ToString();
                    this.Picasa = reader["picasa"].ToString();
                    this.Myspace = reader["myspace"].ToString();
                    this.Lastfm = reader["lastfm"].ToString();
                    this.Youtube = reader["youtube"].ToString();
                    this.Foursquare = reader["foursquare"].ToString();
                    this.Website1 = reader["website1"].ToString();
                    this.Website2 = reader["website2"].ToString();
                    this.Website3 = reader["website3"].ToString();
                    this.isTherePhoto = Convert.ToBoolean(reader["isTherePhoto"]);
                    this.isPhotoUsed = Convert.ToBoolean(reader["isPhotoUsed"]);
                    this.PhotoUrl1 = reader["photoUrl1"].ToString();
                    this.PhotoUrl2 = reader["photoUrl2"].ToString();
                }
            }
        }

        // checked
        public void RetrieveUserInfoWithUserNameForAccount()
        {
            string ssql = "SELECT * FROM [Users] as U INNER JOIN [User-Displays] as UD ON U.userId=UD.userId WHERE username=@username ";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@username", UserName);


            SqlDataReader reader = DatabaseHelper.ExecuteSelectSql(cmd);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    this.UserId = Int32.Parse(reader["userId"].ToString());
                    this.Email = reader["email"].ToString();
                    this.Password = reader["password"].ToString();
                    this.UserName = reader["username"].ToString();
                    this.NameSurname = reader["nameSurname"].ToString();
                    this.Title = reader["title"].ToString();
                    this.ShortDesc = reader["shortDesc"].ToString();
                    if (reader["categoryId"].ToString() != "")
                    {
                        this.CategoryId = Convert.ToInt32(reader["categoryId"].ToString());
                    }
                    else
                    {
                        this.CategoryId = 0;
                    }
                    this.Facebook = reader["facebook"].ToString();
                    this.Twitter = reader["twitter"].ToString();
                    this.Blogger = reader["blogger"].ToString();
                    this.Wordpress = reader["wordpress"].ToString();
                    this.Linkedin = reader["linkedin"].ToString();
                    this.Tumblr = reader["tumblr"].ToString();
                    this.Formspring = reader["formspring"].ToString();
                    this.Stumbleupon = reader["stumbleupon"].ToString();
                    this.Flickr = reader["flickr"].ToString();
                    this.Picasa = reader["picasa"].ToString();
                    this.Myspace = reader["myspace"].ToString();
                    this.Lastfm = reader["lastfm"].ToString();
                    this.Youtube = reader["youtube"].ToString();
                    this.Foursquare = reader["foursquare"].ToString();
                    this.Website1 = reader["website1"].ToString();
                    this.Website2 = reader["website2"].ToString();
                    this.Website3 = reader["website3"].ToString();
                    this.Instagram = reader["instagram"].ToString();
                    this.Vimeo = reader["vimeo"].ToString();
                    this.Googleplus = reader["googleplus"].ToString();
                    this.Pinterest = reader["pinterest"].ToString();
                    this.Skype = reader["skype"].ToString();
                    this.Soundcloud = reader["soundcloud"].ToString();
                    this.Stackoverflow = reader["stackoverflow"].ToString();
                    this.Github = reader["github"].ToString();
                    this.isTherePhoto = Convert.ToBoolean(reader["isTherePhoto"]);
                    this.isPhotoUsed = Convert.ToBoolean(reader["isPhotoUsed"]);
                    this.PhotoUrl1 = reader["photoUrl1"].ToString();
                    this.PhotoUrl2 = reader["photoUrl2"].ToString();
                }
            }
        }

        public void RetrieveTodaysUserWithCategory(string categoryName)
        {
            string ssql = "SELECT * FROM [Users] as U INNER JOIN ";
            ssql += " [User-Displays] as UD ON U.userId=UD.userId INNER JOIN "; 
            ssql += " [Categories] as C ON U.categoryId=C.categoryId WHERE categoryName=@categoryName AND displayDate=@displayDate AND isActive=1 ";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@categoryName", categoryName);
            cmd.Parameters.AddWithValue("@displayDate", DateTime.Today);


            SqlDataReader reader = DatabaseHelper.ExecuteSelectSql(cmd);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    this.UserId = Int32.Parse(reader["userId"].ToString());
                    this.Email = reader["email"].ToString();
                    this.Password = reader["password"].ToString();
                    this.UserName = reader["username"].ToString();
                    this.NameSurname = reader["nameSurname"].ToString();
                    this.ShortDesc = reader["shortDesc"].ToString();
                    if (reader["categoryId"].ToString() != "")
                    {
                        this.CategoryId = Convert.ToInt32(reader["categoryId"].ToString());
                    }
                    else
                    {
                        this.CategoryId = 0;
                    }
                    this.Facebook = reader["facebook"].ToString();
                    this.Twitter = reader["twitter"].ToString();
                    this.Blogger = reader["blogger"].ToString();
                    this.Wordpress = reader["wordpress"].ToString();
                    this.Linkedin = reader["linkedin"].ToString();
                    this.Tumblr = reader["tumblr"].ToString();
                    this.Formspring = reader["formspring"].ToString();
                    this.Stumbleupon = reader["stumbleupon"].ToString();
                    this.Flickr = reader["flickr"].ToString();
                    this.Picasa = reader["picasa"].ToString();
                    this.Myspace = reader["myspace"].ToString();
                    this.Lastfm = reader["lastfm"].ToString();
                    this.Youtube = reader["youtube"].ToString();
                    this.Foursquare = reader["foursquare"].ToString();
                    this.Website1 = reader["website1"].ToString();
                    this.Website2 = reader["website2"].ToString();
                    this.Website3 = reader["website3"].ToString();
                    this.Instagram = reader["instagram"].ToString();
                    this.Vimeo = reader["vimeo"].ToString();
                    this.Googleplus = reader["googleplus"].ToString();
                    this.Pinterest = reader["pinterest"].ToString();
                    this.Skype = reader["skype"].ToString();
                    this.Soundcloud = reader["soundcloud"].ToString();
                    this.Stackoverflow = reader["stackoverflow"].ToString();
                    this.Github = reader["github"].ToString();
                    this.PhotoUrl1 = reader["photoUrl1"].ToString();
                    this.PhotoUrl2 = reader["photoUrl2"].ToString();
                }
            }
        }

        public static SqlDataReader RetrieveUncontrolled()
        {
            string ssql = "SELECT * FROM [Users] as U INNER JOIN [Categories] as C ON U.categoryId=C.categoryId ";
            ssql += " INNER JOIN [User-Displays] as UD ON U.userId=UD.userId WHERE UD.isControlled='false' ";

            SqlCommand cmd = new SqlCommand(ssql);
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }

        public static SqlDataReader RetrieveControlledWithCategoryId(int categoryId)
        {
            string ssql = "SELECT * FROM [Users] as U INNER JOIN [Categories] as C ON U.categoryId=C.categoryId ";
            ssql += " INNER JOIN [User-Displays] as UD ON U.userId=UD.userId WHERE UD.isControlled='true' ";
            if (categoryId != 0)
            {
                ssql += " AND U.categoryId=@categoryId ";
            }
            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@categoryId", categoryId);
            
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }

        public static SqlDataReader RetrieveAppropriatesWithCategoryId(int categoryId)
        {
            string ssql = @"SELECT * FROM [Users] as U INNER JOIN [Categories] as C ON U.categoryId=C.categoryId
                            INNER JOIN [User-Displays] as UD ON U.userId=UD.userId WHERE UD.isControlled='true' AND isDisplayed='false'
                                AND UD.isAppropriate='true' AND U.categoryId=@categoryId ";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@categoryId", categoryId);
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }

        public static SqlDataReader RetrieveUsersReadyToDisplay()
        {
            string ssql = @"SELECT * FROM [Users] as U INNER JOIN [Categories] as C ON U.categoryId=C.categoryId
                            INNER JOIN [User-Displays] as UD ON U.userId=UD.userId WHERE UD.isControlled='true' AND isDisplayed='false'
                                AND UD.isAppropriate='true' AND displayDate IS NOT NULL 
                                      ORDER BY displayDate ASC ";

            SqlCommand cmd = new SqlCommand(ssql);
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }

        public static string GetRandomUserName()
        {
            string ssql = @"SELECT TOP 1 username FROM [Users] ORDER BY newid()";

            SqlCommand cmd = new SqlCommand(ssql);

            SqlDataReader reader = DatabaseHelper.ExecuteSelectSql(cmd);
            string username = "";
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    username = reader["username"].ToString();
                }
            }
            return username;
        }

        public void GetControlledRandomUserName()
        {
            string ssql = @"SELECT TOP 1 username FROM [Users] as U INNER JOIN
	                        [User-Displays] as UD ON U.userId=UD.userId
		                        WHERE isActive='true' AND isControlled='true' AND isAppropriate='true'  
			                        ORDER BY newid()";

            SqlCommand cmd = new SqlCommand(ssql);

            SqlDataReader reader = DatabaseHelper.ExecuteSelectSql(cmd);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    this.UserName = reader["username"].ToString();
                }
            }
        }

        public static SqlDataReader Search(string item)
        {
            string ssql = @"SELECT * FROM [Users] as U INNER JOIN [Categories] as C ON U.categoryId=C.categoryId  
                                WHERE U.username LIKE @item OR U.nameSurname LIKE @item ";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@item", "%"+item+"%");
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }

        public static SqlDataReader Search(string item,int categoryId)
        {
            string ssql = @"SELECT * FROM [Users] as U INNER JOIN [Categories] as C ON U.categoryId=C.categoryId  
                                WHERE U.username LIKE @item OR U.nameSurname LIKE @item AND U.categoryId=@categoryId ";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@item", "%" + item + "%");
            cmd.Parameters.AddWithValue("@categoryId", categoryId);
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }

        // checked
        public bool UpdateField(string key, string value)
        {
            string ssql = "UPDATE [Users] SET "+ key +"=@value WHERE userId=@userId";

            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@value", value);

            return DatabaseHelper.ExecuteNonQuerySqlWithoutIdentity(cmd);
        }

        

        #endregion
    }
}
